package com.sangmo.fts.training.repo;

import com.sangmo.boot.framework.plugin.dataview.spi.jpa.IJpaDataViewRepo;
import com.sangmo.fts.training.model.Score;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface ScoreRepo extends IJpaDataViewRepo<Score> {

    List<Score> findByTrainingId(Long trainingId);

    Score findFirstByUserIdAndStateOrderByIdDesc(Long userId, String state);

    @Query(value = "SELECT a.* from  score a LEFT JOIN examination b on a.examination_id = b.id where a.user_id = :userId and b.mode = :mode and a.state = 'COMPLETED' ORDER BY a.id DESC limit 1", nativeQuery = true)
    Score findLatestByUserIdAndMode(@Param("userId") Long userId, @Param("mode") String mode);

    @Query("SELECT COUNT(id) FROM Score where examinationId = :examinationId")
    int countByExaminationId(@Param("examinationId") Long examinationId);

//    @Query(value = "SELECT sum(case when (0<=a.final_mark and a.final_mark<=60) then 1 else 0 end) AS lessThan,sum(case when (60<a.final_mark and a.final_mark<=70) then 1 else 0 end) AS between60And70,sum(case when (70<a.final_mark and a.final_mark<=80) then 1 else 0 end) AS between70And80,sum(case when (80<a.final_mark and a.final_mark<=90) then 1 else 0 end) AS between80And90,sum(case when (90<a.final_mark and a.final_mark<=100) then 1 else 0 end) AS greaterThan90 FROM score a WHERE a.examination_id = :examinationId ", nativeQuery = true)
//    ScoreDistributionView findDistributionByExaminationId(@Param("examinationId")Long examinationId);
}
